﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ServiceStack.OrmLite;
using LPY.LeHealth.DTO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace LPY.LeHealth.DAL
{
    public class OrderDAL : GenericBaseDAL<Order>
    {
        
        /// <summary>
        /// 更新订单状态
        /// </summary>
        /// <param name="orderId">订单Id</param>
        /// <param name="state">订单状态</param>
        /// <returns></returns>
        public bool UpdateState(int orderId, int state)
        {
            using (var cnn=DbConnection)
            {
                var result= cnn.UpdateOnly<Order>(()=>new Order { State = state }, where: m => m.Id == orderId);
                return result > 0;
            }
        }

        public bool UpdateSate(string payCode,int state)
        {
            using (var cnn = DbConnection)
            {
                var result = cnn.UpdateOnly<Order>(() => new Order { State = state }, where: m => m.PayCode == payCode);
                return result > 0;
            }
        }

        /// <summary>
        /// 获取订单列表
        /// </summary>
        /// <param name="uid">用户Id</param>
        /// <param name="state">订单状态</param>
        /// <returns></returns>
        public List<Order> GetList(int uid,int state)
        {
            using (var cnn=DbConnection)
            {
                var sql = cnn.From<Order>()
                    .Where(m => m.UserId == uid && m.State == state)
                    .OrderByDescending(m=>m.CreateDate);
                //return cnn.Select(sql);  
                var result = cnn.Select(sql);

                //if (result.Count < 1)
                //{
                //    return null;
                //}

                //var ComboId = from s in result
                //              select s.Details[0].ComboId;

                //var sql2 = cnn.From<ComboDTO>()
                //    .Where(m => Sql.In(m.Id, ComboId));

                //var d2 = cnn.Select(sql2);

                //var hosIds = from s in d2
                //             select s.HospitalId;

                //var hosSql = cnn.From<HospitalDTO>()
                //    .Where(m => Sql.In(m.Id, hosIds));
                //var hos = cnn.Select(hosSql);

                //var combos = (from s in d2
                //              select s).ToList();
                //combos.Merge(hos);

                //result[0].Details[0].Combo = combos.FirstOrDefault();

                return result;
            }
        }

        public List<Order> GetList(int uid)
        {
            using (var cnn=DbConnection)
            {
                //var sql = cnn.From<Order>()
                //    .Where(m => m.UserId == uid)
                //    .OrderByDescending(m => m.CreateDate);
                //return cnn.Select(sql); 

                var sql = cnn.From<Order>()
                     .Where(m => m.UserId == uid).OrderByDescending(m => m.CreateDate);
                var result = cnn.Select(sql);

                if (result.Count < 1)
                {
                    return null;
                }

                var ComboId = from s in result
                           select s.Details[0].ComboId;

                var sql2 = cnn.From<ComboDTO>()
                    .Where(m => Sql.In(m.Id, ComboId));

                var d2 = cnn.Select(sql2);

                var hosIds = from s in d2
                           select s.HospitalId;

                var hosSql = cnn.From<HospitalDTO>()
                    .Where(m => Sql.In(m.Id, hosIds));
                var hos = cnn.Select(hosSql);

                var combos = (from s in d2
                              select s).ToList();
                combos.Merge(hos);

                result[0].Details[0].Combo = combos.FirstOrDefault();

                return result;
            }
        }


        /// <summary>
        /// 根据用户名与该用户的订单Id获取该订单信息
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Order> GetIndentList(int uid,int id)
        {
            using (var cnn = DbConnection)
            {
                
                var sql = cnn.From<Order>()
                     .Where(m => m.UserId == uid&&m.Id==id).OrderByDescending(m => m.CreateDate);
                var result = cnn.Select(sql);

                if(result.Count<1)
                {
                    return null;
                }

                var ComboId = from s in result
                              select s.Details[0].ComboId;

                var sql2 = cnn.From<ComboDTO>()
                    .Where(m => Sql.In(m.Id, ComboId));

                var d2 = cnn.Select(sql2);

                var hosIds = from s in d2
                             select s.HospitalId;

                var hosSql = cnn.From<HospitalDTO>()
                    .Where(m => Sql.In(m.Id, hosIds));
                var hos = cnn.Select(hosSql);

                var combos = (from s in d2
                              select s).ToList();
                combos.Merge(hos);

                result[0].Details[0].Combo = combos.FirstOrDefault();

                return result;
            }
        }


        /// <summary>
        /// 根据Id获取完整订单信息
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public Order GetById(int orderId)
        {
            using (var cnn = DbConnection)
            {
                var sql = cnn.From<Order>()
                     .Where(m => m.Id == orderId);
                var result = cnn.LoadSingleById<Order>(orderId);

                var orderDetailsSql = cnn.From<OrderDetail>()
                    .Where(m => m.OrderId == orderId.ToString());
                var ods = cnn.LoadSelect(orderDetailsSql);

                var hosIds = from s in ods
                             select s.Combo.HospitalId;

                var hosSql = cnn.From<HospitalDTO>()
                    .Where(m => Sql.In(m.Id, hosIds));
                var hos = cnn.Select(hosSql);

                var combos = (from s in ods
                              select s.Combo).ToList();
                combos.Merge(hos);

                ods.Merge(combos);

                result.Details = ods;

                return result;
            }
        }

        /// <summary>
        /// 获取订单类型总数
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public Dictionary<int,int> GetOrderStateCount(int uid)
        {
            using (var cnn=DbConnection)
            {
                var sql = "select State, count(*) as Count from t_order where UserId=@UID group by State";
                return cnn.Dictionary<int,int>(sql, new { UID = uid });
            }
        }

        /// <summary>
        /// 获取订单数（待付款）
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public int GetOrderCount(int uid)
        {
            using (var cnn = DbConnection)
            {

                var sql = cnn.From<Order>()
                    .Where(m => m.UserId == uid && m.State==0);
                var count = cnn.Select(sql).Count;
                return count;
                
            }
        }

        /// <summary>
        /// 显示医院列表
        /// </summary>
        /// <returns></returns>
        public List<Order> ShowOrderList()
        {

            var sqlExpression = DbConnection.From<Order>().Where(C => true);

            var rtv = DbConnection.Select(sqlExpression).ToList();

            return rtv;
        }

		/// <summary>
		/// 根据用户ID判断该用户是否用购买记录
		/// </summary>
		/// <param name="UserID">用户编号</param>
		/// <returns>返回购买记录</returns>
		public List<Order> GetOrder(int UserID)
		{
			//查询表达式
			var Order = DbConnection.From<Order>()
				  .Where(m => m.UserId == UserID);

			//查询
			var rtv = DbConnection.Select(Order).ToList();
			return rtv;
		}


        /// <summary>
        /// 根据订单id删除该订单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int del_order(int id)
        {
            using (var cnn = DbConnection)
            {
                var sql = cnn.From<Order>()
                     .Where(m => m.Id == id);
                return cnn.Delete(sql);
            }
        }
   


         /// <summary>
        /// 添加团体订单
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public long Add_tuanorder(TuanOrderDTO t)
        { 
            using(var cnn=DbConnection)
            {
                var tid = cnn.Insert<TuanOrderDTO>(t,selectIdentity:true);
                return tid;
            }
        }
         /// <summary>
        /// 根据id获取团检订单信息
        /// </summary>
        /// <returns></returns>
        public List<TuanOrderDTO> GettuanorderbyId(int id)
        {
            using (var cnn = DbConnection)
            {
                var sql = cnn.From<TuanOrderDTO>()
                    .Where(m => m.Id == id);

                var rtv = cnn.Select(sql).ToList();
                return rtv;
            }
        }
        /// <summary>
        /// 获取最新的订单编号
        /// </summary>
        /// <returns></returns>
        public string OrderString()
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();

                string sqlstr = "select top 1 Ordercode from t_order order by Id desc";

                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adp.Fill(table);

                string orderid = table.Rows[0]["Ordercode"].ToString();
                return orderid;
            }
        }
        /// <summary>
        /// 写入订单详情表
        /// </summary>
        /// <param name="code"></param>
        /// <param name="userid"></param>
        /// <param name="codeid"></param>
        /// <param name="counts"></param>
        /// <returns></returns>
        public bool InsertOrderdetails(string code,int userid,int codeid,int counts)
        {
             string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();

                string insert = "insert into t_orderdetail values('" + code + "','" + userid + "','" + codeid + "','" + counts + "')";

                SqlCommand cmd = new SqlCommand(insert,conn);

                return cmd.ExecuteNonQuery()>0;
            }
        }
    }
}
